import paramiko
import psycopg2
import pathlib

# get sentinel2 tile list
def get_province_tiles(province):
	con = psycopg2.connect(
	database="sentinel2",
	user="postgres",
	password="12345",
	host="192.168.2.172",
	port="5432"
	)
	cur = con.cursor()
	sql = f"""
	SELECT "中国有效哨兵格网分布wgs84".mgrs
	FROM china_sy
	JOIN "中国有效哨兵格网分布wgs84" 
	on st_intersects(china_sy.geom,"中国有效哨兵格网分布wgs84".geom)
	WHERE  china_sy.qhmc = '{province}' 
	"""
	cur.execute(sql)
	con.commit()
	tiles=[record[0] for record in cur.fetchall()]
	tiles = [tile.upper() for tile in tiles]
	cur.close()
	con.close()
	return tiles

# start processing
def ssh_client():

    province = input("请输入省份(必选,例如：河南省): ")
    while province == "":
        province = input("请输入省份(必选,例如：河南省): ")
    # tiles = get_province_tiles(province)
    # tiles = "-".join(tiles)

    tiles = "43SED-43SFD-43SGD-43SFC-43SGC-44SKJ-44SKG-44SLG-44SMG-44SNF-44TLL-44TML-44TNL-44TPL-44TQL-44TLK-44TMK-44TNK-45SUC-45TTF-45TUF-45TVF-45TWF-44SLJ"	
    # tiles = tiles.split("-")
    print(tiles)
    
    year = input("请输入年份（必选, 例如：2022）：")
    while not year:
        year = input("请输入年份（必选, 例如：2022）：")

    month = input("请输入月份（必选, 例如：3）：")
    while not month:
        month = input("请输入月份（必选, 例如：3）：")

    local_folder = input("请输入本地存放查询结果目录（必选）：")
    while not local_folder:
        local_folder = input("请输入本地存放查询结果目录（必选）：")
    xlsx_name = f"{province}_{year}-{month}.xlsx"
    print("请选择主机：")
    print("(192.168.1.82)请输入1")
    print("(192.168.2.172)请输入2")
    remote_ip = input("请选择服务器(必选,例如：1): ")
    while remote_ip == "":
        remote_ip = input("请选择服务器(必选,例如：1): ")
    if remote_ip == "1":
        print("你选择服务器：192.168.1.82")
        hostname="192.168.1.82"
        port="22"
        username="root"
        password="000000"
        python_path=r"/home/fengyangyang/python_env_1/bin/python"
        script_path=r"/home/fengyangyang/projection_1/GetL1cInfoXlsx.py"
        remote_folder = input("请输入远程l1c的zip存放目录（必选）：")
        while not remote_folder:
            remote_folder = input("请输入远程l1c的zip存放目录（必选）：")
        folder_remote = r"/83To82/Yang/data/temp"
        path_remote = str(pathlib.PurePosixPath(folder_remote, xlsx_name))
        params = [
            f"{python_path}",
            f"{script_path}",
            f"--province {province}",
            f"--tiles {tiles}",
            f"--year {year}",
            f"--month {month}",
            f"--remote_folder {remote_folder}",
            f"--path {path_remote}"
        ]

    elif remote_ip == "2":
        print("你选择服务器：192.168.2.172")
        hostname="192.168.2.172"
        port="22"
        username="root"
        password="DELL123dell"
        python_path=r"/home/冯洋洋/PythonEnvironment/RasterShapeEnv/bin/python"
        script_path=r"/home/冯洋洋/PythonProjection/RasterShape/GetSentinel2L1cZipInfo.py"
        folder_remote = r"/home/遥感技术部/遥感数据/冯洋洋"
        path_remote =  str(pathlib.PurePosixPath(folder_remote, xlsx_name))
        params = [
            f"{python_path}",
            f"{script_path}",
            f"--province {province}",
            f"--tiles {tiles}",
            f"--year {year}",
            f"--month {month}",
            f"--path {path_remote}"
        ]
    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy)
    ssh.connect(
        hostname=hostname,
        port=port,
        username=username,
        password=password)
    command = " ".join(params)
    stdin, stdout, stderr = ssh.exec_command(
        command, 
        get_pty=True, 
        # bufsize=1
        )
    for line in iter(stdout.readline,""): 
        print(line)
    ssh.close()

    transport = paramiko.Transport((hostname, int(port)))
    transport.connect(username=username, password=password)
    sftp = paramiko.SFTPClient.from_transport(transport)
    path_local = str(pathlib.PureWindowsPath(local_folder, xlsx_name))
    print(path_local)
    sftp.get(path_remote, path_local)
    sftp.close()
    transport.close()
    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy)
    ssh.connect(
        hostname=hostname,
        port=port,
        username=username,
        password=password)
    ssh.exec_command(f"find {path_remote} && rm -f {path_remote}")
    ssh.close()
    print("end!")
    return

if __name__ == "__main__":
    ssh_client()